<?php
class Application_Model_Mapper_Instance extends Application_Model_Mapper_Abstract
{
	/**
	* Get Db table object
	*
	* @return Model_DbTable_Instance
	*/
	protected $_dbName;
	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->_db_ovis_name.'.instances';
	}

	public function getDbTable()
	{
		if (NULL === $this->_dbTable) {
			$this->setDbTable('Application_Model_DbTable_Instance');
		}
		return $this->_dbTable;
	}

	/**
	* @param Zend_Db_Table_Row_Abstract $row Db table row
	*
	* @return Model_Instance|null Instance object or NULL
	* @throws Exception
	*/
	public function mapData($row)
	{
		if (is_null($row)) {
			return NULL;
		}
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$entry = new Application_Model_Instance();
		$entry->setInstanceId($row['instanceid'])
		->setHandle($row['handle'])
		->setStockLocation($row['stocklocation'])
		->setEmail($row['email'])
		->setCreatedBy($row['created_by'])
		->setUpdatedBy($row['updated_by'])
		->setCreatedDate($row['created_date'])
		->setUpdatedDate($row['updated_date'])
		->setLogo($row['logo'])
		->setConnected($row['connected'])
		->setInstanceName($row['instancename'])
		->setOtrsGroup($row['otrs_group']);
		return $entry;
	}

	/**
	* Save Instance object in database
	*
	* @param Model_Instance $instance
	*
	* @return int The primary key of the row inserted or the number of rows updated
	*/
	public function save($instance)
	{
		parent::save($instance);
		$data = array(
				'instanceid'      => $instance->getInstanceId(),
				'handle'          => $instance->getHandle(),
				'stocklocation'   => $instance->getStockLocation(),
				'email'           => $instance->getEmail(),
				'created_by'      => $instance->getCreatedBy(),
				'updated_by'      => $instance->getUpdatedBy(),
				'created_date'    => $instance->getCreatedDate(),
				'updated_date'    => $instance->getUpdatedDate(),
				'logo'            => $instance->getLogo(),
				'connected'       => $instance->getConnected(),
				'instancename'    => $instance->getInstanceName(),
				'otrs_group'      => $instance->getOtrsGroup()
		);
		try {
			if (NULL === ($id = $instance->getInstanceId())) {
				unset($data['instanceid']);
				$instance->setInstanceId($this->getDbTable()->insert($data));
				return $instance->getInstanceId();
			} else {
				return $this->getDbTable()->update($data, array('instanceid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}

	/**
	* Fetch all Instance objects
	*
	* @return Model_Instance[] Array of Instance objects
	*/
	public function fetchAll()
	{
		$resultSet = $this->getDbTable()->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}

	/**
	* put your comment there...
	*
	* @param mixed $instanceId
	* @return Model_DbTable_Instance
	*/
	public function deleteRow($instanceId)
	{
		return $this->getDbTable()->delete(array('instanceid = ?' => $instanceId));
	}
	/**
	* Finds a Instance by Id
	*
	* @param  int $instanceId Channel Id
	*
	* @return Model_Instance Instance object
	*/
	public function findById($instanceId)
	{
		$select = $this->getDbTable()->select()->where('instanceid = ?', $instanceId);
		$row    = $this->getDbTable()->fetchRow($select);
		return $this->mapData($row);
	}

	public function getList($instanceId = null)
	{
		//order by
		$orderBy = " ORDER BY instanceid desc ";
		$where = array();
		if($instanceId != null)
		{
			$where[] = 'i.instanceid not in('.$instanceId.') ';
		}
		$where[]  = '(s.isarchived = 0 or s.isarchived is null)';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		//get vendor list
		$row = $this->loadAll('
				SELECT 
					* 
				from 
					instances i
				INNER JOIN 
					stocklocations s on s.stocklocationid = i.stocklocation
				'.$where.$orderBy);
		return $row;
	}

	public function getInstanceByStockLocation($stockLocation = null)
	{
		//initializa where
		$where = array();
		if ($stockLocation != null) {
			$where[] = 'i.stocklocation='.$stockLocation;
		}
		$where[] = '(s.isarchived = 0 or s.isarchived is null)';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		$limit = " LIMIT 1 ";
		//get vender
		$instance= $this->fetchObject('
					SELECT 
						* 
					FROM 
						instances i
					INNER JOIN
						stocklocations s on s.stocklocationid = i.stocklocation
					'.$where.$limit);
		return $instance;
	}

	public function getById($instanceId = null)
	{
		//initializa order by
		$where= '';
		if($instanceId != null){
			$where = ' where instanceid = '.$instanceId.' and (s.isarchived = 0 or s.isarchived is null)';
		}
		//get all record not paging
		$row = $this->fetchObject('
					SELECT 
						* 
					FROM 
						'.$this->_dbName.' i
					INNER JOIN
						stocklocations s on s.stocklocationid = i.stocklocation'
					.$where);
	
		return $row;
	}

	/**
	 * Get user select
	 * @param array $params
	 * @return Zend_Db_Table_Select
	 */
	public function getInstanceList($limit = '', $limitstart = 0, $filterParam = array()) {
		$where = array();
		if ($filterParam['instancename']) {
			$where[] = 'i.instancename like "%' . $filterParam['instancename'].'%"';
		}
		if ($filterParam['handle']) {
			$where[] = 'i.handle like "%' . $filterParam['handle'].'%"';
		}
		if ($filterParam['fullname']) {
			$where[] = 'a.fullname like "%' . $filterParam['fullname'].'%"';
		}
		//$where[] = '(sl.isarchived = 0 or sl.isarchived is null)';
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
	
		$getPagination = Sss_Ultility_Common::getPagination($limit, $limitstart, $filterParam['orderfield'],$filterParam['ordertype'],$filterParam['page'], 'instancename');
		$orderBy    = $getPagination['orderby'];
		$limit      = $getPagination['limit'];
		$limitstart = $getPagination['limitstart'];
		$pagString  = $getPagination['pagstring'];
		
		$query = '	SELECT 
						DISTINCT i.*,sl.stocklocationname from ' . $this->_dbName .' i
					INNER JOIN 
						stocklocationinstances si on si.instance = i.instanceid
					INNER JOIN 
						stocklocations sl on sl.stocklocationid = i.stocklocation
					LEFT JOIN 
						stocklocationcompanyowners sc on sc.stocklocation = i.stocklocation
					LEFT JOIN 
						accounts a on a.accountid = sc.companyowner and a.accounttype='.Sss_Ultility_Setting::ACCOUNT_CUSTOMER;
		
		$rows = $this->loadAll($query. $where . $orderBy.$pagString, false);
	
		$rowAll = $this->loadAll($query . $where . $orderBy, false);
	
		$result             = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));
	
		//return list
		$result->list       = $rows;
		return $result;
	}

	public function getAccountByInstance($instanceId, $accountId)
	{
		$prefixDB = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER);
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		$where= array();
		if($accountId != null){
			$where[] = " accountid =".$accountId;
		}
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
					SELECT 
						a.*, r.roleid, r.rolename 
					FROM 
						".$accountTableName.' a
					LEFT JOIN 
						'.$prefixDB.'.assignments ass on ass.account = a.accountid
					LEFT JOIN 
						'.$prefixDB.'.roles r on r.roleid = ass.role'
					.$where);
		return $user;
	}
	
	public function getRoleByAccount($instanceId, $assignmentAccount=null)
	{
		$assignmentTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.assignments';
		//initializa order by
		$where= '';
		if($assignmentAccount != null)
			$where = ' where account = '.$assignmentAccount;
	
		//get all record not paging
		$row = $this->fetchObject('
					SELECT 
						role 
					FROM ' 
						. $assignmentTableName . 
					$where);
	
		return $row;
	}
	
	public function getByRoleId($instanceId, $roleId = null, $feature = null)
	{
		$permissionTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.permissions';
		//initializa order by
		$where = array();
		if($roleId != null)
			$where[] = ' role = '.$roleId;
	
		if($feature != null)
			$where[] = ' feature = "'.$feature.'"';
		$where = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
	
		//get all record not paging
		$row = $this->loadAll('
					SELECT 
						* 
					FROM ' 
						. $permissionTableName . $where);
		return $row;
	}
	
	public function getConectedInstanceList($instanceId = null)
	{
		//order by
		$orderBy = " order by instanceid desc ";
		$where = array();
		if($instanceId != null)
		{
			$where[] = 'i.instanceid in('.$instanceId.') ';
		}
		$where[]  = '(s.isarchived = 0 or s.isarchived is null)';
		$where[]  = 'connected=1';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		//get vendor list
		$row = $this->loadAll('
					SELECT 
						* 
					FROM 
						'.$this->_dbName.' i
					INNER JOIN
						stocklocations s on s.stocklocationid = i.stocklocation
					'.$where.$orderBy);
		return $row;
	}
	
	public function getAccountsByInstance($instanceId, $limit = '', $limitstart = 0, $filterParam = array(), $isList = null)
	{
		$prefixDB = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER);
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		$where = array();
		if ($filterParam['username']) {
			$where[] = 'u.username like "%' . $filterParam['username'].'%"';
		}
		if ($filterParam['fullname']) {
			$where[] = 'u.fullname like "%' . $filterParam['fullname'].'%"';
		}
		if ($filterParam['rolename']) {
			$where[] = 'r.rolename like "%' . $filterParam['rolename'].'%"';
		}
		if($isList != null){
			$where[] = " u.username <> '".Sss_Ultility_CommonViewParamater::USER_NAME_SHEPHERD_DEFAULT."'";
		}
		if (isset($filterParam['isactivated'])) {
			$where[] = ' u.isactivated = '.$filterParam['isactivated'];
		}
		$where[] = " isarchived = 0 ";
	
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
	
		$getPagination = Sss_Ultility_Common::getPagination($limit, $limitstart, $filterParam['orderfield'],$filterParam['ordertype'],$filterParam['page'], 'accountid');
		$orderBy    = $getPagination['orderby'];
		$limit      = $getPagination['limit'];
		$limitstart = $getPagination['limitstart'];
		$pagString  = $getPagination['pagstring'];
	
		$query = '	SELECT 
						* 
					FROM 
						' . $accountTableName .' u
					LEFT JOIN ' . $prefixDB . '.assignments a on a.account = u.accountid
					LEFT JOIn ' . $prefixDB . '.roles r on r.roleid = a.role';
		
		$rows = $this->loadAll($query . $where . $orderBy.$pagString);
		$rowAll = $this->loadAll($query . $where . $orderBy);
	
		$result             = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));
		//return list
		$result->list       = $rows;
		return $result;
	}
	
	public function resetAccountofInstance($instanceId, $accountId, $activatedCode)
	{
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		$query = "UPDATE $accountTableName SET activatecode='$activatedCode', isactivated=0 WHERE accountid=$accountId";
		$this->executeQuery($query);
	}

	public function getInstanceByStock($stockLocation = null)
	{
		//initializa where
		$where = array();
		if ($stockLocation != null) {
			$where[] = 'si.stocklocation='.$stockLocation;
		}
		$where[] = '(s.isarchived = 0 or s.isarchived is null)';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		$limit = " LIMIT 1 ";
		
		//get vender
		$instance= $this->fetchObject('
						SELECT 
							* 
						FROM 
							'.$this->_dbName.' i
						INNER JOIN 
							stocklocationinstances si on si.instance = i.instanceid
						INNER JOIN 
							stocklocations s on s.stocklocationid = si.stocklocation
						'.$where.$limit);
		return $instance;
	}

	public function getInstanceByHandle($handle = null)
	{
		//initializa where
		$where = array();
	
		if ($handle != null) {
			$where[] = "i.handle='".$handle."'";
		}
		$where[] = '(s.isarchived = 0 or s.isarchived is null)';
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$limit = " LIMIT 1 ";
	
		//get vender
		$instance= $this->fetchObject('
						SELECT 
							* 
						FROM 
							instances i
						INNER JOIN 
							stocklocations s ON s.stocklocationid = i.stocklocation
						'.$where.$limit);
		return $instance;
	}
	
	public function updateAccount($accountId, $instanceId, $listCustomerIds)
	{
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		$query = "UPDATE $accountTableName SET customer_ids='$listCustomerIds' WHERE accountid = $accountId";
		$this->executeQuery($query);
	}

	public function checkEsixtedColumns($columnName = '')
	{
		$query = "SHOW COLUMNS FROM `instances` LIKE '".$columnName."'";
		$res = $this->fetchObject($query, false);
		if($res == false)
		{
			$addCol = "ALTER TABLE `instances` add column ".$columnName." int";
			$this->executeQuery($addCol, false);
		}
	}

	function getInstanceByAccountId($accountId = null)
	{
		//initializa where
		$where = array();
	
		if ($accountId != null) {
			$where[] = 'sc.companyowner='.$accountId;
		}
		$where[] = '(s.isarchived = 0 or s.isarchived is null)';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
	
		//get instances
		$instances = $this->fetchObject('
						SELECT 
							i.* 
						FROM 
							instances i
						INNER JOIN 
							stocklocations s on s.stocklocationid = i.stocklocation
						INNER JOIN 
							stocklocationcompanyowners sc on sc.stocklocation = s.stocklocationid
						INNER JOIN 
							accounts a on a.accountid = sc.companyowner and a.accounttype = 3
						'.$where
		);
		return $instances;
	}

	public function getUser($instanceId, $userName = null)
	{
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		$where= array();
		if($userName != null)
			$where[] = " username = '".$userName."'";
		$where[] = " isactivated = 1 ";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
						SELECT 
							* 
						FROM
							 ".$accountTableName.
						$where
				);
		return $user;
	}
}
